/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package MySqlAccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author LNThanh
 */
public class Article 
{


    public List<ArticleInfo> GetByDate(Timestamp begin, Timestamp end)
    {
        String query = "SELECT  POST_DATE, POST_CONTENT, ID" +
                " FROM WP_POSTS WHERE POST_TYPE = 'post' AND POST_DATE >= '" + begin +
                "' AND POST_DATE < '" + end + "'";
        return ExcuteQuery(query);
    }

    public List<ArticleInfo> GetItemByAuthor(int id)
    {
        String query = "SELECT POST_DATE, POST_CONTENT, ID FROM WP_POSTS WHERE POST_AUTHOR = " + id + " AND POST_TYPE = \"POST\"";
        return ExcuteQuery(query);
    }    
    
    public List<ArticleInfo> GetAllItem()
    {
        String query = "SELECT POST_DATE, POST_CONTENT, ID FROM WP_POSTS WHERE POST_TYPE = \"POST\"";
        return ExcuteQuery(query);
    }

    public void UpdateDate(int postID, Timestamp date)
    {
        String query = "update wp_posts set post_date = ? where id = ?";
        Connection conn = DataAcessHelper.GetConnection();
        try
        {
            PreparedStatement preparedStmt = conn.prepareStatement(query);
            preparedStmt.setInt(2, postID);
            preparedStmt.setTimestamp(1, date);

          // execute the java preparedstatement
            preparedStmt.executeUpdate();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }

    }

    public List<ArticleInfo> ExcuteQuery(String query)
    {        
        List<ArticleInfo> result = new ArrayList<ArticleInfo>();
            
        try
        {       
            DataAcessHelper da = DataAcessHelper.GetInstance();
            ResultSet rs =  da.ExcuteQuery(query);            
            
            //int idxContent = rs.findColumn("post_content");            
            int idxID = rs.findColumn("ID");
            int idxPosDate = rs.findColumn("POST_DATE");

            rs.beforeFirst();
            
            while(rs.next())
            {
                int id = rs.getInt(idxID);
                String url = PermalinkMapping.MappingByID(id);               
                
                if (!url.trim().isEmpty())
                {
                    ArticleInfo articleInfo = new ArticleInfo();
                    articleInfo.url = url;
                    articleInfo.id = id;
                    articleInfo.post_date = rs.getTimestamp(idxPosDate);

                    result.add(articleInfo);
                }                
            }
            rs.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        
        return result;
    }
    
    public static void main(String[] argvs)
    {
        Article article = new Article();        
        List<ArticleInfo> lstArticle = article.GetAllItem();
        System.out.println("Number of row:" + lstArticle.size());
       
        for (ArticleInfo articleInfo : lstArticle)
        {
            System.out.println("----------------------------------------------------");
            System.out.println("url: " + articleInfo.url);
        }
    }    
    
}
